create  table jms_dm.dm_terminal_deliver_punctuality_rate_plan_sum_dt
(
final_deliver_network_code   varchar(100) COMMENT '派件网点code' ,
end_franchisee_code varchar(100) comment '末端加盟商code',
end_agent_code   varchar(100) COMMENT '末端代理区code' ,
end_agent_name  varchar(100) COMMENT '末端代理区' ,
end_center_code   varchar(100) COMMENT '末端中心code' ,
end_center_name  varchar(100) COMMENT '末端中心名字' ,
end_city_id   varchar(100) COMMENT '城市id' ,
end_city_name  varchar(100) COMMENT '城市名字' ,
end_franchisee_name varchar(100) comment '末端加盟商',
final_deliver_network_name   varchar(100) COMMENT '派件网点名字' ,
order_source_code   varchar(100) COMMENT '订单来源code' ,
order_source_name  varchar(100) COMMENT '订单来源' ,
is_common   int(8) COMMENT '是否共配' ,
need_deliver_count   int(8) COMMENT '应派件总量' ,
 punctuality_sum  int(8) COMMENT '及时派件总量' ,
one_planned_departure_time   varchar(100) COMMENT '一班规划接件时间' ,
one_plan_warehouse_end_time   varchar(100) COMMENT '一班规划出仓时间' ,
one_need_deliver_count   int(8) COMMENT '一班应出仓总量' ,
one_punctuality_sum   int(8) COMMENT '一班准点出仓量' ,
one_delay_30_minutes   int(8) COMMENT '一班延误30分钟量' ,
one_delay_60_minutes   int(8) COMMENT '一班延误30分钟量' ,
one_delay_120_minutes   int(8) COMMENT '一班延误120分钟总量' ,
two_planned_departure_time   varchar(100) COMMENT '二班规划接件时间' ,
two_plan_warehouse_end_time   varchar(100) COMMENT '二班规划出仓时间' ,
two_need_deliver_count   int(8) COMMENT '二班应出仓总量' ,
two_punctuality_sum   int(8) COMMENT '二班准点出仓量' ,
two_delay_30_minutes   int(8) COMMENT '二班延误30分钟量' ,
two_delay_60_minutes   int(8) COMMENT '二班延误30分钟量' ,
two_delay_120_minutes   int(8) COMMENT '二班延误120分钟总量' ,
is_township   int(6) COMMENT '1乡镇 0 非乡镇' ,
date_time date COMMENT '业务日期',
deliver_800_count int(8) comment '应派件量',
on_time_deliver_800_count int(8) comment '准点应派件量',
deliver_830_count int(8) comment '应派件量',
on_time_deliver_830_count int(8) comment '准点应派件量',
deliver_900_count int(8) comment '应派件量',
on_time_deliver_900_count int(8) comment '准点应派件量',
       three_planned_departure_time varchar(120) comment '三班规划接件时间',
       three_plan_warehouse_end_time varchar(120) comment '三班规划出仓时间',
       three_need_deliver_count int(8) comment'三班应出仓总量' ,
       three_punctuality_sum    int(8) comment'三班准点出仓量',
       three_delay_30_minutes   int(8) comment'三班延误30分钟量',
       three_delay_60_minutes   int(8) comment'三班延误60分钟量',
       three_delay_120_minutes  int(8) comment'三班延误120分钟总量'
)
ENGINE=OLAP
DUPLICATE KEY(final_deliver_network_code,end_franchisee_code,end_agent_code)
comment '派件及时率规划派件维度汇总'
PARTITION BY RANGE (date_time) (
   START ("2023-07-01") END ("2023-08-30") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(final_deliver_network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-180",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);

alter table jms_dm.dm_terminal_deliver_punctuality_rate_plan_sum_dt add column (
        final_sign_user_code varchar(100) comment '派件员code'
       ,final_sign_user_name varchar(100) comment '派件员'
    ) ;